package com.guda.mp.utils;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class CopyOfReplenishListExcelUtil {

	private String filepath;
	private List<Map<String, String>> colsConfig = new ArrayList<Map<String, String>>();
	private List<List<String>> dataList = new ArrayList<List<String>>();

	public CopyOfReplenishListExcelUtil(String filepath) {
		super();
		this.filepath = filepath;
	}

	public CopyOfReplenishListExcelUtil() {
		super();
	}

	public String wirte() {
		return wirte(false);
	}

	public String wirte(boolean isAppend) {
		OutputStream os = null;
		WritableWorkbook workbook = null;
		try {
			WritableSheet sheet;
			if (new File(this.filepath).exists()) {
				Workbook workbook2 = Workbook.getWorkbook(new File(this.filepath));
				workbook = Workbook.createWorkbook(new File(this.filepath), workbook2);
				sheet = workbook.getSheet("1");
			} else {
				os = new FileOutputStream(this.filepath, isAppend);
				workbook = Workbook.createWorkbook(os);
				sheet = workbook.createSheet("1", 0);
			}

			int startRowNum = sheet.getRows();

			// 插入图片
//			startRowNum = 12;

			// 设置字体;
			WritableFont font1 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

			WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
			WritableCellFormat cellFormat2 = new WritableCellFormat(font2);

			// 设置背景颜色;
			cellFormat1.setBackground(Colour.GRAY_25);
			// 设置自动换行;
			cellFormat1.setWrap(true);
			// 设置边框;
			cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
			// 设置文字居中对齐方式;
			cellFormat1.setAlignment(Alignment.CENTRE);
			// 设置垂直居中;
			cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);

			// 设置自动换行;
			cellFormat2.setWrap(true);
			// 设置边框;
			cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
			// 设置文字居中对齐方式;
			cellFormat2.setAlignment(Alignment.CENTRE);
			// 设置垂直居中;
			cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);

			// 开始读取配置文件生成表头
			if (!isAppend) {
				for (int i = 0; i < this.colsConfig.size(); i++) {
					Map<String, String> map = this.colsConfig.get(i);
					String title = map.get("title");
					String width = map.get("width");
					sheet.addCell(new Label(i, startRowNum, title, cellFormat1));// Lable第一个参数是列，第二个参数是行，第三个参数是文字
					sheet.setColumnView(i, Integer.parseInt(width));
					sheet.setRowView(startRowNum, 500);
				}
			}

			// 循环生成数据，必须要与表头的数据对应
			for (int i = 0; i < dataList.size(); i++) {
				int index = startRowNum + i + 1;
				// 设置列
				List<String> col = dataList.get(i);
				for (int j = 0; j < col.size(); j++) {
					sheet.addCell(new Label(j, index, col.get(j), cellFormat2));
				}
			}
			
			startRowNum = startRowNum + dataList.size();
			
			String imgPath = "/Users/afei/Downloads/qr1.png";
			File imgFile = new File(imgPath);
			startRowNum = addPictureToExcel(sheet, imgFile, startRowNum, 1);
	
			// 将内容写到输出流中，然后关闭工作区，最后关闭输出流
			workbook.write();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (workbook != null)
					workbook.close();
				if (os != null)
					os.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return filepath;
	}

	/**
	 * 插入图片到EXCEL
	 * 
	 * @param picSheet
	 *            sheet
	 * @param pictureFile
	 *            图片file对象
	 * @param cellRow
	 *            行数
	 * @param cellCol
	 *            列数
	 * @throws Exception
	 *             例外
	 */
	private int addPictureToExcel(WritableSheet picSheet, File pictureFile, int cellRow, int cellCol) throws Exception {
		// 开始位置
		double picBeginCol = (cellCol <= 0 ? 1 : cellCol) - 1;
		double picBeginRow = (cellCol <= 0 ? 1 : cellCol) - 1;
		// 图片时间的高度，宽度
		double picCellWidth = 0.0;
		Double picCellHeight = 0.0d;
		// 读入图片
		BufferedImage picImage = ImageIO.read(pictureFile);
		// 取得图片的像素高度，宽度
		int picWidth = picImage.getWidth();
		int picHeight = picImage.getHeight();

		// 计算图片的实际宽度
		int picWidth_t = picWidth * 32; // 具体的实验值，原理不清楚。
		for (int x = 0; x < 1234; x++) {
			int bc = (int) Math.floor(picBeginCol + x);
			// 得到单元格的宽度
			int v = picSheet.getColumnView(bc).getSize();
			double offset0_t = 0.0;
			if (0 == x)
				offset0_t = (picBeginCol - bc) * v;
			if (0.0 + offset0_t + picWidth_t > v) {
				// 剩余宽度超过一个单元格的宽度
				double ratio_t = 1.0;
				if (0 == x) {
					ratio_t = (0.0 + v - offset0_t) / v;
				}
				picCellWidth += ratio_t;
				picWidth_t -= (int) (0.0 + v - offset0_t);
			} else { // 剩余宽度不足一个单元格的宽度
				double ratio_r = 0.0;
				if (v != 0)
					ratio_r = (0.0 + picWidth_t) / v;
				picCellWidth += ratio_r;
				break;
			}
		}
		
		// 计算图片的实际高度
		int picHeight_t = picHeight * 15;
		for (int x = 0; x < 1234; x++) {
			int bc = (int) Math.floor(picBeginRow + x);
			// 得到单元格的高度
			int v = picSheet.getRowView(bc).getSize();
			double offset0_r = 0.0;
			if (0 == x)
				offset0_r = (picBeginRow - bc) * v;
			if (0.0 + offset0_r + picHeight_t > v) {
				// 剩余高度超过一个单元格的高度
				double ratio_q = 1.0;
				if (0 == x)
					ratio_q = (0.0 + v - offset0_r) / v;
				picCellHeight += ratio_q;
				picHeight_t -= (int) (0.0 + v - offset0_r);
			} else {// 剩余高度不足一个单元格的高度
				double ratio_m = 0.0;
				if (v != 0)
					ratio_m = (0.0 + picHeight_t) / v;
				picCellHeight += ratio_m;
				break;
			}
		}
		// 生成一个图片对象。
		WritableImage image = new WritableImage(picBeginCol, picBeginRow, picCellWidth, picCellHeight, pictureFile);
		// 把图片插入到sheet
		picSheet.addImage(image);
		
		return picCellHeight.intValue() + 1;
	}

	public void addRow(List<String> list) {
		this.dataList.add(list);
	}

	public List<List<String>> getDataList() {
		return dataList;
	}

	public void setDataList(List<List<String>> dataList) {
		this.dataList = dataList;
	}

	public CopyOfReplenishListExcelUtil addCol(String title, int width) {
		Map<String, String> m = new HashMap<String, String>();
		m.put("title", title);
		m.put("width", width + "");
		colsConfig.add(m);

		return this;
	}

	public List<Map<String, String>> getColsConfig() {
		return this.colsConfig;
	}

	public List<String> readExcel(InputStream is) throws BiffException, IOException {
		List<String> strList = new ArrayList<String>();
		// 2、声明工作簿对象
		Workbook rwb = Workbook.getWorkbook(is);
		// 3、获得工作簿的个数,对应于一个excel中的工作表个数
		rwb.getNumberOfSheets();

		Sheet oFirstSheet = rwb.getSheet(0);// 使用索引形式获取第一个工作表，也可以使用rwb.getSheet(sheetName);其中sheetName表示的是工作表的名称
		// System.out.println("工作表名称：" + oFirstSheet.getName());
		int rows = oFirstSheet.getRows();// 获取工作表中的总行数
		int columns = oFirstSheet.getColumns();// 获取工作表中的总列数
		for (int i = 0; i < rows; i++) {
			String str = new String();
			for (int j = 0; j < columns; j++) {
				Cell oCell = oFirstSheet.getCell(j, i);// 需要注意的是这里的getCell方法的参数，第一个是指定第几列，第二个参数才是指定第几行
				if (columns - j == 1) {
					str = str + oCell.getContents();
				} else {
					str = str + oCell.getContents() + ",";
				}
			}
			strList.add(str);
		}
		return strList;
	}
}
